package com.zy.dao.impl;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.junit.Test;

import com.sun.org.apache.regexp.internal.recompile;
import com.zy.dao.VisitDao;
import com.zy.entity.Customer;
import com.zy.entity.Linkman;
import com.zy.entity.User;
import com.zy.entity.Visit;
import com.zy.utils.JDBCutil;

import jdk.nashorn.internal.ir.RuntimeNode.Request;

public class VisitDaoImpl implements VisitDao{

	@Override
	public List<Visit> queryall() {
		Connection con = null;
		String sql = "select vid,vaddress,vcontent,custName,username" + 
				" from t_visit,t_customer,t_user" + 
				" where uvid=uid and cvid=cid";

		try {
			// 从配置文件里面取值
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			ResultSet rs = ps.executeQuery();

			// 创建集合
			ArrayList<Visit> arrayList = new ArrayList<>();
			// 填充集合
			while (rs.next()) {
				Visit visit1 = new Visit();
				visit1.setVid(rs.getInt(1));
				visit1.setVaddress(rs.getString(2));
				visit1.setVcontent(rs.getString(3));
				visit1.getCustomer().setCustName(rs.getString(4));
				visit1.getUser().setUsername(rs.getString(5));
				arrayList.add(visit1);
			}
			// 返回list
			return arrayList;

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	@Test
	public void query() {
		List<Visit> visits = queryall();
		for (Visit visit : visits) {
			System.out.println(visit.toString());
		}
	}
	@Override
	public List<Customer> getCustomer() {
		
		return null;
	}
	@Override
	public List<User> getUser() {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public Visit queryById(int id) {
		try {
			String sql = "select * from t_visit where vid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setInt(1, id);
			
			ResultSet rs = ps.executeQuery();
			Visit visit = new Visit();
			

			while (rs.next()) {
				visit.setVid(rs.getInt(1));
				visit.setVaddress(rs.getString(2));
				visit.getUser().setUid(rs.getInt(3));
				visit.getCustomer().setCid(rs.getInt(4));
				visit.setVcontent(rs.getString(5));
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return visit;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public void update(Visit visit) {
		try {
			String sql = "update t_visit set vaddress=?,uvid=?,cvid=?,vcontent=? where vid=?";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1,visit.getVaddress());
			ps.setInt(2, visit.getUser().getUid());
			ps.setInt(3, visit.getCustomer().getCid());
			ps.setString(4, visit.getVcontent());
			ps.setInt(5,  visit.getVid());
			
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	@Override
	public List<Visit> queryByAddress(String address) {
		try {
			String str = "'%"+address+"%'";
			String sql = "select custName,username,vaddress,vcontent from t_visit,t_customer,t_user where uvid=uid and cvid=cid and vaddress like "+str;
			System.out.println(sql);
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			
			ResultSet rs = ps.executeQuery();
			List<Visit> visits = new ArrayList<>();
			

			while (rs.next()) {
				Visit visit = new Visit();
				visit.getCustomer().setCustName(rs.getString(1));
				visit.getUser().setUsername(rs.getString(2));
				visit.setVaddress(rs.getString(3));
				visit.setVcontent(rs.getString(4));
				
				visits.add(visit);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return visits;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public void insert(Visit visit) {
		try {
			String sql = "insert into t_visit values(null,?,?,?,?)";
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);

			// 执行
			ps.setString(1, visit.getVaddress());
			ps.setInt(2, visit.getUser().getUid());
			ps.setInt(3, visit.getCustomer().getCid());
			ps.setString(4,visit.getVcontent());
			
			ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
	@Override
	public List<Visit> queryByCU(int cvid, int uvid) {
		try {
			String sql = "select custName,username,vaddress,vcontent from t_visit,t_customer,t_user where uvid=uid and cvid=cid and cvid=? and uvid=?";
			System.out.println(sql);
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			ps.setInt(1, cvid);
			ps.setInt(2, uvid);
			ResultSet rs = ps.executeQuery();
			List<Visit> visits = new ArrayList<>();
			

			while (rs.next()) {
				Visit visit = new Visit();
				visit.getCustomer().setCustName(rs.getString(1));
				visit.getUser().setUsername(rs.getString(2));
				visit.setVaddress(rs.getString(3));
				visit.setVcontent(rs.getString(4));
				
				visits.add(visit);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return visits;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public List<Visit> queryByCUA(int cvid, int uvid, String vaddress) {
		try {
			String str = "'%"+vaddress+"%'";
			String sql = "select custName,username,vaddress,vcontent from t_visit,t_customer,t_user where uvid=uid and cvid=cid and cvid=? and uvid=? and vaddress like "+str;
			System.out.println(sql);
			Connection con = null;
			Properties properties = new Properties();
			InputStream in = CustomerDaoImpl.class.getClassLoader().getResourceAsStream("jdbc.txt");
			properties.load(in);
			PreparedStatement ps = JDBCutil.getPS(con, properties.getProperty("jdbc.url"),
					properties.getProperty("jdbc.user"), properties.getProperty("jdbc.pwd"), sql);
			ps.setInt(1, cvid);
			ps.setInt(2, uvid);
			ResultSet rs = ps.executeQuery();
			List<Visit> visits = new ArrayList<>();
			

			while (rs.next()) {
				Visit visit = new Visit();
				visit.getCustomer().setCustName(rs.getString(1));
				visit.getUser().setUsername(rs.getString(2));
				visit.setVaddress(rs.getString(3));
				visit.setVcontent(rs.getString(4));
				
				visits.add(visit);
			}

			// 关闭资源
			JDBCutil.closeAll(rs, ps, con);
			return visits;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

}
